﻿/*****************************************
 * 
 *  copyright (c) by ylshen
 * 
 *  author : ylshen (syl.lucky@gmail.com)
 *  
 *  created at : 08-06-04
 * 
 **************************************** */

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using JYStockAnalyser.Model;

namespace JYStockAnalyser.DA
{
    public class StockMapper : ORMapper
    {
        public Stock FindStockByNO(string no)
        {
            string filter = String.Format(" WHERE [no] = '{0}'", no);
            return m_findStockByFilter(filter);
        }

        public List<Stock> FindAllStocks()
        {
            string sql = "SELECT kind, [no], [name], province, trade, location, companyName, address, id FROM t_stocks ORDER BY [no]";
            List<Stock> stocks = new List<Stock>();
            using (SqlDataReader reader = Excute_Reader(sql))
            {
                while (reader.Read())
                {
                    Stock stock = new Stock();
                    stock.Kind = GetReaderInt32Value(reader, 0);
                    stock.NO= GetReaderString(reader, 1);
                    stock.Name = GetReaderString(reader, 2);
                    stock.Province = GetReaderString(reader, 3);
                    stock.Trade = GetReaderString(reader, 4);
                    stock.Location = GetReaderString(reader, 5);
                    stock.CompanyName = GetReaderString(reader, 6);
                    stock.address = GetReaderString(reader, 7);
                    stock.Id = GetReaderInt32Value(reader, 8);
                    stocks.Add(stock);
                }
            }
            return stocks;
        }

        public List<string> FindAllStockNOAndNames()
        {
            string sql = "SELECT [no], [name], [kind] FROM t_stocks ORDER BY [no]";
            List<string> nos = new List<string>();
            using (SqlDataReader reader = Excute_Reader(sql))
            {
                while (reader.Read())
                {
                    int kind = GetReaderInt32Value(reader, 2);
                    string no = GetReaderString(reader, 0) + "  " + GetReaderString(reader, 1);
                    if (kind == 1)//若是金融类股票，则在其名称后加上"(金融)"
                    {
                        no += " (金融)";
                    }
                    nos.Add(no);
                }
            }
            return nos;
        }

        public void InsertStock(Stock stock)
        {
            string sql = "IF EXISTS (SELECT * FROM t_stocks WHERE no = @no) " +
                "BEGIN " +
                "RAISERROR 130007 '' " +
                "END " +
                "ELSE BEGIN " +
                "INSERT t_stocks([no], name, kind, companyName, province, trade, address, location) " +
                "VALUES (@no, @name, @kind, @companyName, @province, @trade, @address, @location) " +
                "END";
            SqlConnection cn = CreateConnection();
            SqlCommand cmd = new SqlCommand(sql, cn);     
            cmd.Parameters.Add(new SqlParameter("@id", stock.Id));
            cmd.Parameters.Add(new SqlParameter("@no", stock.NO));
            cmd.Parameters.Add(new SqlParameter("@name", stock.Name));
            cmd.Parameters.Add(new SqlParameter("@kind", stock.Kind));
            cmd.Parameters.Add(new SqlParameter("@companyName", stock.CompanyName));
            cmd.Parameters.Add(new SqlParameter("@province", stock.Province));
            cmd.Parameters.Add(new SqlParameter("@trade", stock.Trade));
            cmd.Parameters.Add(new SqlParameter("@address", stock.address));
            cmd.Parameters.Add(new SqlParameter("@location", stock.Location));

            cn.Open();
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (System.Data.SqlClient.SqlException sqlErr)
            {
                if (sqlErr.Number == 130007)
                {
                    throw new ORException(ORException.ERROR_NAME_EXISTS);                 
                }
                throw new ORException(ORException.ERROR_OTHER);
            }
            catch (System.Exception err)
            {
                Loger.WriteErrorLog(err.ToString());
                throw new ORException();
            }
            finally
            {
                cn.Close();
            }


        }

        public void UpdateStock(Stock stock)
        {
            string sql = "IF EXISTS(SELECT * FROM t_stocks WHERE no = @no AND id <> @id)" +
                "BEGIN " +
                "RAISERROR 130007 '' " +
                "END " +
                "ELSE BEGIN " +
                "UPDATE t_stocks SET [no] = @no, name = @name, kind = @kind, companyName = @companyName, province = @province, trade = @trade, address = @address, location = @location WHERE id = @id " +
                "END";
            SqlConnection cn = CreateConnection();
            SqlCommand cmd = new SqlCommand(sql, cn);   
            cmd.Parameters.Add(new SqlParameter("@id", stock.Id));
            cmd.Parameters.Add(new SqlParameter("@no", stock.NO));
            cmd.Parameters.Add(new SqlParameter("@name", stock.Name));
            cmd.Parameters.Add(new SqlParameter("@kind", stock.Kind));
            cmd.Parameters.Add(new SqlParameter("@companyName", stock.CompanyName));
            cmd.Parameters.Add(new SqlParameter("@province", stock.Province));
            cmd.Parameters.Add(new SqlParameter("@trade", stock.Trade));
            cmd.Parameters.Add(new SqlParameter("@address", stock.address));
            cmd.Parameters.Add(new SqlParameter("@location", stock.Location));

            cn.Open();
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (System.Data.SqlClient.SqlException sqlErr)
            {
                if (sqlErr.Number == 130007)
                {
                    throw new ORException(ORException.ERROR_NAME_EXISTS);
                }
                throw new ORException(ORException.ERROR_OTHER);
            }
            catch (System.Exception err)
            {
                Loger.WriteErrorLog(err.ToString());
                throw new ORException();
            }
            finally
            {
                cn.Close();
            }
        }

        public void DeleteStock(int id)
        {
            string sql = "DELETE t_stocks WHERE id = @id";
            SqlConnection cn = CreateConnection();
            SqlCommand cmd = new SqlCommand(sql, cn);
            cmd.Parameters.Add(new SqlParameter("@id", id));
            cn.Open();
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (System.Exception err)
            {
                Loger.WriteErrorLog(err.ToString());
                throw new ORException();
            }
            finally
            {
                cn.Close();
            }
        }

        public List<string> FindAllTrades()
        {
            string sql = "SELECT distinct trade FROM t_stocks ORDER BY trade";
            List<string> trades = new List<string>();
            using (SqlDataReader reader = Excute_Reader(sql))
            {
                while (reader.Read())
                {
                    trades.Add(GetReaderString(reader, 0));
                }
            }
            return trades;
        }

        #region private methods

        private Stock m_findStockByFilter(string filter)
        {
            List<Stock> stocks = m_findStocksByFilter(filter);
            return stocks.Count == 0 ? null : stocks[0];
        }

        private List<Stock> m_findStocksByFilter(string filter)
        {
            string sql = "SELECT [no], [name], companyName, province, trade, address, location, kind FROM t_stocks" + filter;
            List<Stock> stocks = new List<Stock>();
            using (SqlDataReader reader = Excute_Reader(sql))
            {
                while (reader.Read())
                {
                    Stock stock = new Stock();
                    stock.NO = GetReaderString(reader, 0);
                    stock.Name = GetReaderString(reader, 1);          
                    stock.CompanyName = GetReaderString(reader, 2);
                    stock.Province = GetReaderString(reader, 3);
                    stock.Trade = GetReaderString(reader, 4);
                    stock.address = GetReaderString(reader, 5);
                    stock.Location = GetReaderString(reader, 6);
                    stock.Kind = GetReaderInt32Value(reader, 7);
                    stocks.Add(stock);
                }
            }
            return stocks;
        }

        #endregion
    }
}
